package com.yellowpage.omc.datacollect.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.map.LinkedMap;

import com.yellowpage.ndc.dao.AddressDao;
import com.yellowpage.ndc.dao.CustomerDao;
import com.yellowpage.ndc.dao.ListingDao;
import com.yellowpage.ndc.dao.ManageRegionDao;
import com.yellowpage.ndc.data.Address;
import com.yellowpage.ndc.data.Customer;
import com.yellowpage.ndc.data.Listing;
import com.yellowpage.omc.datacollect.dao.DataCollectImportLogDao;
import com.yellowpage.omc.datacollect.dao.DataVerifyDao;
import com.yellowpage.omc.datacollect.data.DataCollectImportLog;
import com.yellowpage.omc.datacollect.data.DataVerify;
import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;
import com.yellowpage.white.service.BaseServiceImpl;

public class DataVerifyServiceImpl extends BaseServiceImpl implements
		DataVerifyService {
	private DataVerifyDao dataVerifyDao;
	private CustomerDao customerDao;
	private ListingDao listingDao;
	private AddressDao addressDao;
	private DataCollectImportLogDao dataCollectImportLogDao;
	private ManageRegionDao manageRegionDao;

	public void setDataVerifyDao(DataVerifyDao dataVerifyDao) {
		this.dataVerifyDao = dataVerifyDao;
	}

	/* Smooth generate Method */
	public DataVerify getDataVerify(Long id) {
		return dataVerifyDao.get(id);
	}

	public List<DataVerify> getAllDataVerify() {
		return dataVerifyDao.getAll();
	}

	public List<Map> queryDataVerifyByPage(Map queryMap, PageRender pageRender) {

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						          "SELECT  c.SEQ, b.BATCH_NUM,"
								+ "        a.DATA_VERIFY_ID , a.DATA_COLLECT_ID , a.VERIFY_RESULT , a.LINK , a.TEL_TYPE , a.TEL , a.CITY,  a.REGION_CODE, "
								+ "        a.CLASS01 , a.CLASS02 , a.ADDR , a.FAX , a.TEL02 , a.CREATE_TIME , a.LAST_UPDATE_TIME , "
								+ "        a.NOTE , a.NAME , a.ICON , a.INFO , "
								+ "        (select c.CUST_ID  from db_ndc.LISTING l, db_ndc.CUSTOMER c, db_ndc.ADDRESS ADDR " +
								  "          WHERE l.CUST_ID = c.CUST_ID and l.ADDR_ID = ADDR.ADDR_ID and l.LIST_ID = a.LIST_ID   ) as NDC_CUST_ID," +
								  "        (select c.NAME1    from db_ndc.LISTING l, db_ndc.CUSTOMER c,  db_ndc.ADDRESS ADDR " +
								  "          WHERE l.CUST_ID = c.CUST_ID and l.ADDR_ID = ADDR.ADDR_ID and l.LIST_ID = a.LIST_ID  ) as NDC_CUST_NAME," +
								  "        (select ADDR.ADDR  from db_ndc.LISTING l,db_ndc.ADDRESS ADDR  " +
								  "          WHERE l.ADDR_ID = ADDR.ADDR_ID and l.LIST_ID = a.LIST_ID  ) as NDC_ADDRESS," +
								  "        (select odt.name from OMC_DIC_TABLE odt where odt.dic_name = 'DATA_VERIFY_OPT' and odt.value = a.OPT_TYPE) as OPT_TYPE , " +
								  "        (select odt.name from OMC_DIC_TABLE odt where odt.dic_name = 'DATA_VERIFY_STATUS' and odt.value = a.STATUS) as STATUS ,    "
								+ "       (select display_name from CA_USER where user_id = a.create_user) as create_user,"
								+ "       (select display_name from CA_USER where user_id = a.last_update_user) as last_update_user "
								+ "FROM OMC_DATA_VERIFY a , OMC_DATA_COLLECT c, OMC_DATA_COLLECT_BATCH b "
								+ " WHERE a.DATA_COLLECT_ID = c.DATA_COLLECT_ID "
								+ "   and c.DATA_COLLECT_BATCH_ID = b.DATA_COLLECT_BATCH_ID ")
				.addCondition(" and b.BATCH_NUM = :batchNum ")
				.addCondition(" and c.SEQ = :seq ")
				.addCondition(" and a.DATA_VERIFY_ID = :dataVerifyId ")
				.addCondition(" and a.DATA_COLLECT_ID = :dataCollectId ")
				.addCondition(" and a.VERIFY_RESULT = :verifyResult ")
				.addCondition(" and a.LINK = :link ")
				.addCondition(" and a.TEL_TYPE = :telType ")
				.addCondition(" and a.TEL = :tel ")
				.addCondition(" and a.CITY = :city ")
				.addCondition(" and a.CLASS01 = :class01 ")
				.addCondition(" and a.CLASS02 = :class02 ")
				.addCondition(" and a.ADDR = :addr ")
				.addCondition(" and a.FAX = :fax ")
				.addCondition(" and a.TEL02 = :tel02 ")
				.addCondition(" and CREATE_USER = :createUser ")
				.addCondition(
						" and CREATE_TIME = to_date(:createTime ,'yyyy-MM-dd') ")
				.addCondition(" and LAST_UPDATE_USER = :lastUpdateUser ")
				.addCondition(
						" and LAST_UPDATE_TIME = to_date(:lastUpdateTime ,'yyyy-MM-dd') ")
				.addCondition(" and a.NOTE = :note ")
				.addCondition(" and a.NAME = :name ")
				.addCondition(" and a.ICON = :icon ")
				.addCondition(" and a.INFO = :info ")
				.addCondition(" and a.STATUS = :status ");

		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
	}

	public Map getStatusMap() {
		Map dataSourceMap = new LinkedMap();

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(" select NAME, VALUE from OMC_DIC_TABLE where DIC_NAME = 'DATA_VERIFY_STATUS' ");

		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());

		for (int i = 0; i < resultList.size(); i++) {
			dataSourceMap.put(resultList.get(i).get("VALUE"), resultList.get(i)
					.get("NAME"));
		}

		return dataSourceMap;
	}
	
	public Map getOptTypeMap() {
		Map dataSourceMap = new LinkedMap();

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(" select NAME, VALUE from OMC_DIC_TABLE where DIC_NAME = 'DATA_VERIFY_OPT' ");

		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());

		for (int i = 0; i < resultList.size(); i++) {
			dataSourceMap.put(resultList.get(i).get("VALUE"), resultList.get(i)
					.get("NAME"));
		}

		return dataSourceMap;
	}

	public DataVerify getDataVerifyByCollectId(String dataCollectId) {
		return dataVerifyDao.getDataVerifyByCollectId(dataCollectId);
	}

	public List<Map> getApprovedDataByCollectId(String dataCollectId) {
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(" SELECT a.DATA_COLLECT_ID,  a.LINK, a.TEL_TYPE,"
						+ "       a.TEL, a.CITY,  a.CLASS01, a.CLASS02, a.ADDR, a.FAX,"
						+ "       a.TEL02,"
						+ "        (select c.CUST_ID  from db_ndc.LISTING l, db_ndc.CUSTOMER c, db_ndc.ADDRESS ADDR " +
						  "          WHERE l.CUST_ID = c.CUST_ID and l.ADDR_ID = ADDR.ADDR_ID and l.LIST_ID = a.LIST_ID    ) as NDC_CUST_ID," +
						  "        (select c.NAME1    from db_ndc.LISTING l, db_ndc.CUSTOMER c,  db_ndc.ADDRESS ADDR " +
						  "          WHERE l.CUST_ID = c.CUST_ID and l.ADDR_ID = ADDR.ADDR_ID and l.LIST_ID = a.LIST_ID  ) as NDC_CUST_NAME," +
						  "        (select ADDR.ADDR  from db_ndc.LISTING l,db_ndc.ADDRESS ADDR  " +
						  "          WHERE l.ADDR_ID = ADDR.ADDR_ID and l.LIST_ID = a.LIST_ID ) as NDC_ADDRESS,"
						+ "       a.NOTE,  a.NAME,  a.ICON, a.INFO,  a.STATUS, c.SEQ, b.BATCH_NUM "
						+ "  FROM OMC_DATA_VERIFY a, OMC_DATA_COLLECT c, OMC_DATA_COLLECT_BATCH b"
						+ " WHERE a.DATA_COLLECT_ID = c.DATA_COLLECT_ID"
						+ "   and c.DATA_COLLECT_BATCH_ID = b.DATA_COLLECT_BATCH_ID"
						+ "   and a.DATA_COLLECT_ID = " + dataCollectId);

		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());

		return resultList;
	}

	public void saveDataVerify(DataVerify dataVerify) {

		String regionCode = manageRegionDao
				.getRegionCodeByRegionName(dataVerify.getCity());

		if (regionCode == null) {
			regionCode = "000000";
		}
		
		if(dataVerify.getRegionCode() == null){
			dataVerify.setRegionCode(regionCode);
		}		

		dataVerifyDao.save(dataVerify);
	}

	public void updateDataVerify(DataVerify dataVerify) {
		dataVerifyDao.update(dataVerify);
	}

	public void delete(List deleteList) {
		dataVerifyDao.remove(deleteList);
	}

	public void inSingle(DataVerify dataVerify) {
		log.error("inSingle222222222 in...............................");
		Connection conn = null;
		PreparedStatement pstmt = null;

		//Listing listing = listingDao.getListingByRegionTel(dataVerify.getTel(), dataVerify.getRegionCode());
		

		DataCollectImportLog importLog = new DataCollectImportLog();
		String content = "";

		String regionCode = manageRegionDao
				.getRegionCodeByRegionName(dataVerify.getCity());

		if (regionCode == null) {
			regionCode = "000000";
		}

		if ("ADD".equals(dataVerify.getOptType()))  {
			Listing listing = new Listing();
			Customer customer = new Customer();
			Address address = new Address();

			//获取SEQ
			QueryAssistantSQL sqlAssistant = baseDao
					.newQueryAssistantSQL("select sn.seq_num as seq_num from OMC_SEQ_NUM sn where sn.seq_name = 'OMC_CUST_ID' ");

			List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
					new HashMap());

			Integer codeNum = (Integer) resultList.get(0).get("SEQ_NUM");

			try {

				baseDao.getConnection()
						.createStatement()
						.execute(
								"update  OMC_SEQ_NUM sn "
										+ "   set sn.seq_num = "
										+ (codeNum.intValue() + 1)
										+ "  where sn.seq_name = 'OMC_CUST_ID'");

			} catch (Exception e) {
				e.printStackTrace();
			}

			String custId = "";

			for (int i = 0; i < (12 - codeNum.toString().length()); i++) {
				custId = custId + "0";
			}
			custId = "CJ" + custId + codeNum.toString();

			customer.setCustId(custId);
			customer.setGroupId(custId);
			customer.setName1(dataVerify.getName());
			customer.setModifyTime(new Date());
			customer.setModifyEmpId(dataVerify.getLastUpdateUser());
			customerDao.save(customer);

			address.setAddr(dataVerify.getAddr());
			address.setAddrBrief(dataVerify.getAddr());
			address.setRegionCode(regionCode);
			address.setModifyTime(new Date());
			address.setModifyEmpId(dataVerify.getLastUpdateUser());
			addressDao.save(address);

			listing.setTel(dataVerify.getTel());
			listing.setCustId(custId);
			listing.setAddrId(address.getAddrId());
			listing.setFindwayId(0l);
			listing.setListOrder(1);
			listing.setModifyTime(new Date());			
			listing.setModifyEmpId(dataVerify.getLastUpdateUser());
			listingDao.save(listing);

			content = "insert " + customer.getCustId() + "|" + listing.getTel();

		} 
		else if ("UPDATE".equals(dataVerify.getOptType())){
			
			if (dataVerify.getListId() != null) {

				Listing listing = listingDao.get(dataVerify.getListId());

				if (listing != null) {

					Customer customer = customerDao.getCustomerByCustId(listing
							.getCustId());
					Address address = addressDao.get(listing.getAddrId());

					String cName = customer.getName1();
					String cAddress = address.getAddr();

					content = "update " + customer.getCustId() + "|"
							+ listing.getTel() + " set " + cName + "->"
							+ dataVerify.getName() + " | " + cAddress + "->"
							+ dataVerify.getAddr();

					customer.setName1(dataVerify.getName());
					customer.setModifyTime(new Date());
					customer.setModifyEmpId(dataVerify.getLastUpdateUser());
					customerDao.update(customer);

					address.setAddr(dataVerify.getAddr());
					address.setRegionCode(regionCode);
					address.setModifyTime(new Date());
					address.setModifyEmpId(dataVerify.getLastUpdateUser());
					addressDao.update(address);
				} else {

					content = "update " + dataVerify.getDataVerifyId()
							+ " 未找到匹配的List";
				}
			} else {
				content = "update " + dataVerify.getDataVerifyId()
						+ " ListId 为空";
			}
				

		}
		else if ("DELETE".equals(dataVerify.getOptType())){
			content = "delete list " +  dataVerify.getListId() + "|" 
			+ dataVerify.getName()+ "|" 
			+ dataVerify.getAddr();
		}

		importLog.setEvent("NDC入库");
		importLog.setContent(content);
		importLog.setDataCollectId(dataVerify.getDataCollectId());
		importLog.setCreateTime(new Date());
		importLog.setCreateUser(dataVerify.getLastUpdateUser());
		dataCollectImportLogDao.save(importLog);

		dataVerify.setStatus("IMPORTED");
		dataVerifyDao.update(dataVerify);

	}

	public CustomerDao getCustomerDao() {
		return customerDao;
	}

	public void setCustomerDao(CustomerDao customerDao) {
		this.customerDao = customerDao;
	}

	public ListingDao getListingDao() {
		return listingDao;
	}

	public void setListingDao(ListingDao listingDao) {
		this.listingDao = listingDao;
	}

	public AddressDao getAddressDao() {
		return addressDao;
	}

	public void setAddressDao(AddressDao addressDao) {
		this.addressDao = addressDao;
	}

	public DataCollectImportLogDao getDataCollectImportLogDao() {
		return dataCollectImportLogDao;
	}

	public void setDataCollectImportLogDao(
			DataCollectImportLogDao dataCollectImportLogDao) {
		this.dataCollectImportLogDao = dataCollectImportLogDao;
	}

	public ManageRegionDao getManageRegionDao() {
		return manageRegionDao;
	}

	public void setManageRegionDao(ManageRegionDao manageRegionDao) {
		this.manageRegionDao = manageRegionDao;
	}

}